package pkg16b_eca.SQL;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.logging.Level;
import java.util.logging.Logger;
import pkg16b_eca.data.validate.ArrayValidation;
import pkg16b_eca.data.validate.Text;
import se.oru.informatik.InformatikDB;
import se.oru.informatik.InformatikException;

/*Implements IConnection to get an interface of this class. */ /** Read more about interfaces and these functions in the file pkg16b_eca.api.IConnection.java. */
public class Query {
    public Query() throws InformatikException {}
    
    /**Database Connection, this is a database connection, and a string to the location of the database. This variable is used when connecting to the database. (using insert, update, fetchSingle, delete, et cetera.*/
    private final InformatikDB database = new InformatikDB("C:\\ECA.fdb"); //We are assuming that the database is existing with the correct tables when using this program.
    private final boolean debug = false; //If you want debug messages or not when doing anything with the SQL functions.
    
    //@Override
    protected final void SQLInsert(final String query) {
        try {
            if (debug) System.out.println("@ SQLInsert(String) \n    " + query + ";");
            database.insert(Text.getSafeSpecialCharacters(query + ";"));
        } catch (InformatikException ex) {
            Logger.getLogger(Query.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    //@Override
    protected final void SQLInsert(final String tableName, final String[] rows, final String[] values) {
        try {
            if (debug) System.out.println("@ SQLInsert(String, String[], String[]) \n    " + "INSERT INTO " + tableName + " (" + rows + ") VALUES (" + values + ");");
            database.insert(Text.getSafeSpecialCharacters("INSERT INTO " + tableName + " (" + rows + ") VALUES (" + values + ");"));
        } catch (InformatikException ex) {
            Logger.getLogger(Query.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    //@Override
    protected final void SQLUpdate(final String query) {
        try {
            if (debug) System.out.println("@ SQLUpdate(String): \n    " + query + ";");
            database.update(Text.getSafeSpecialCharacters(query + ";"));
        } catch (InformatikException ex) {
            Logger.getLogger(Query.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    //@Override
    protected final void SQLUpdate(final String tableName, final String[] columns, final String[] whereColIs) {
        try {
            if (debug) System.out.println("@ SQLUpdate(String, String[], String[]): \n    " + "UPDATE " + tableName + " SET " + columns + " WHERE " + whereColIs + ";");
            database.update(Text.getSafeSpecialCharacters("UPDATE " + tableName + " SET " + columns + " WHERE " + whereColIs + ";"));
        } catch (InformatikException ex) {
            Logger.getLogger(Query.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    //@Override
    protected final String SQLSelect(final String query) {
        try {
            if (debug) System.out.println("@ SQLSelect(String): \n    " + query + ";");
            return Text.getSpecialCharacters(database.fetchSingle(Text.getSafeSpecialCharacters(query + ";")));
        } catch (InformatikException ex) {
            Logger.getLogger(Query.class.getName()).log(Level.SEVERE, null, ex);
        }
        return "error-SQLSelect";
    }
    
    //@Override
    protected final String SQLSelect(final String[] value, final String[] tableName, final String[] where) {
        try {
            String fixedWhere = " (" + where[0] + ")"; //Example on input: 'Hej' = Tabell.text
            String fixedTable = " (" + tableName[0] + ")"; //Example on input: tabell
            String fixedValue = " (" + value[0] + ")"; //Example on input: tabell.text, tabell.namn
            
            if (where.length >= 1){
                fixedWhere = "";
                for (String item : where) {
                    fixedWhere += " AND (" + item + ")"; //Example on input: 'Hej' = Tabell.text
                }
            }
            
            if (tableName.length >= 1){
                fixedTable = "";
                for (String item : tableName) {
                    fixedTable += ", (" + item + ")"; //Example on input: tabell
                }
                fixedTable = fixedTable.substring(1); //Remove the comma.
            }
            
            if (value.length >= 1){
                fixedValue = "";
                for (String item : where) {
                    fixedValue += ", (" + item + ")"; //Example on input: tabell.text, tabell.namn
                }
                fixedValue = fixedValue.substring(1); //Example on input: tabell.text, tabell.namn
            }
            if (debug) System.out.println("@ SQLSelect(String[], String[], String[]): \n    " + "SELECT " + fixedValue + " FROM " + fixedTable + " WHERE" + fixedWhere + ";");
            return Text.getSpecialCharacters(database.fetchSingle(Text.getSafeSpecialCharacters("SELECT " + fixedValue + " FROM " + fixedTable + " WHERE" + fixedWhere + ";")));
        } catch (InformatikException ex) {
            Logger.getLogger(Query.class.getName()).log(Level.SEVERE, null, ex);
        }
        return "error-SQLSelect[2]";
    }
    
    //@Override
    protected final ArrayList<String> SQLSelectList(final String query) {
        try {
            if (debug) System.out.println("@ SQLSelectList(String): \n    " + query + ";");
            ArrayList<String> tmpList = new ArrayList<>();
            for (String i : database.fetchColumn(Text.getSafeSpecialCharacters(query + ";"))) {
                tmpList.add(Text.getSpecialCharacters(i));
            }
            return tmpList;
        } catch (InformatikException ex) {
            Logger.getLogger(Query.class.getName()).log(Level.SEVERE, null, ex);
        }
        return new ArrayList<>();
    }
    
    //@Override
    protected final ArrayList<ArrayList<String>> SQLSelectMatrix(final String query) {
        try {
            if (debug) System.out.println("@ SQLSelectMatrix(String): \n    " + query + ";");
            return ArrayValidation.SelectMatrix(database.fetchMatrix(Text.getSafeSpecialCharacters(query) + ";"));
        } catch (InformatikException ex) {
            Logger.getLogger(Query.class.getName()).log(Level.SEVERE, null, ex);
        }
        return new ArrayList<>();
    }
    
    //@Override
    protected final HashMap<String, String> SQLSelectRow(final String query) {
        try {
            if (debug) System.out.println("@ SQLSelectRow(String): \n    " + query + ";");
            return ArrayValidation.SelectRow(database.fetchRow(Text.getSafeSpecialCharacters(query) + ";"));
        } catch (InformatikException ex) {
            Logger.getLogger(Query.class.getName()).log(Level.SEVERE, null, ex);
        }
        return new HashMap<>();
    }
    
    //@Override
    protected final ArrayList<HashMap<String, String>> SQLSelectRows(final String query) {
        try {
            if (debug) System.out.println("@ SQLSelectRows(String): \n    " + query + ";");
            return ArrayValidation.SelectRows(database.fetchRows(Text.getSafeSpecialCharacters(query) + ";"));
        } catch (InformatikException ex) {
            Logger.getLogger(Query.class.getName()).log(Level.SEVERE, null, ex);
        }
        return new ArrayList<>();
    }
    
    //@Override
    protected final void SQLDelete(final String query) {
        try {
            if (debug) System.out.println("@ SQLDelete(String): \n    " + query + ";");
            database.delete(Text.getSafeSpecialCharacters(query + ";"));
        } catch (InformatikException ex) {
            Logger.getLogger(Query.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    //@Override
    protected final void SQLDelete(final String tableName, final String where) {
        try {
            if (debug) System.out.println("@ SQLDelete(String, String): \n    " + "DELETE FROM " + tableName + " WHERE " + where + ";");
            database.delete(Text.getSafeSpecialCharacters("DELETE FROM " + tableName + " WHERE " + where + ";"));
        } catch (InformatikException ex) {
            Logger.getLogger(Query.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    //@Override
    protected final String SQLGetAutoIncrement(final String tabell, final String kolumn) {
        try {
            if (debug) System.out.println("@ SQLGetAutoIncrement(String, String): \n    " + tabell + " : " + kolumn + ";");
            return database.getAutoIncrement(tabell, Text.getSafeSpecialCharacters(kolumn));
        } catch (InformatikException ex) {
            Logger.getLogger(Query.class.getName()).log(Level.SEVERE, null, ex);
        }
        return "1";
    }
}
